package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.dto.bank.card.CreateBankCardDto
import com.opennews.openplatform.familyexpenseapi.dto.bank.card.UpdateBankCardDto
import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import jakarta.validation.Valid
import org.jooq.DSLContext
import org.springframework.stereotype.Service

@Service
class BankCardService(
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val bankService: BankService,
    private val incomeExpenseDetailService: IncomeExpenseDetailService,
) {
    /**
     * Queries all bank cards basic info which associated with provided account group.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records is return in one trip.
     * @param pageIndex:      Where to take rows.
     * @return The list of map instance which contains keys below.
     * id: String of bank card id.
     * name: String of bank card name.
     * isCreditCard: Bool to indicate if this is a credit card.
     * enabled: Bool to indicate if this card is enabled.
     * statementDate: Number string of the day of statement.
     * bankName: String of the bank name.
     * bankShortName: String of bank short name.
     * bankLogoUrl: String of bank logo url.
     */
    fun queryBasicInfoByAccountGroup(accountGroupId: String, pageSize: Int?, pageIndex: Int?): List<Map<String, Any>> {
        // Queries bank card by account group id.
        val query = dslContext
            .select(
                BANK_CARD.ID.`as`("id"),
                BANK_CARD.NAME.`as`("name"),
                BANK_CARD.IS_CREDIT_CARD.`as`("isCreditCard"),
                BANK_CARD.ENABLED.`as`("enabled"),
                BANK_CARD.STATEMENT_DATE.`as`("statementDate"),
                BANK.SHORT_NAME.`as`("bankShortName"),
                BANK.NAME.`as`("bankName"),
            )
            .from(BANK_CARD)
            .join(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
            .where(BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .orderBy(BANK_CARD.BANK_ID)

        pageSize?.let { size ->
            pageIndex?.let { index ->
                query.limit(size)
                    .offset(index * size)
            }
        }

        return query.fetch()
            .map { p ->
                val item = p.intoMap()
                item["bankLogoUrl"] = bankService.getLogoUrl(item["bankShortName"] as String?)
                item
            }
    }

    /**
     * Queries all bank cards with basic info which associated with provided account group.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return List of map instance which contains keys below.
     * id: String of bank card id.
     * name: String of bank card name.
     * isCreditCard: Bool to indicate if this is a credit card.
     * enabled: Bool to indicate if this card is enabled.
     * statementDate: Number string of the day of statement.
     * bankName: String of the bank name.
     * bankShortName: String of bank short name.
     * bankLogoUrl: String of bank logo url.
     */
    fun queryAllWithBasicInfoByAccountGroup(accountGroupId: String): List<Map<String, Any>> {
        return queryBasicInfoByAccountGroup(accountGroupId, null, null)
    }

    /**
     * Queries bank card based on provided id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param id:             The string of bank card id.
     * @return The map instance which contains keys below.
     * id: String of bank card id.
     * bankId: String of bank id.
     * bankName: String of the bank name which card belongs to.
     * bankShortName: String of the bank short name.
     * bankLogoUrl: String of the bank logo url.
     * statementDate: The statement date of the card if it is credit card.
     * paymentDueDate: The payment due date of the card if it is credit card.
     * customerServiceNumber: String of the card customer service phone number.
     * isCreditCard: Bool of if this card is credit card.
     * enabled: Bool to indicate if this card is enabled.
     */
    fun queryBankCardInfo(accountGroupId: String, id: String): Map<String, Any> {
        val bankInfo = dslContext
            .select(
                BANK_CARD.ID.`as`("id"),
                BANK.ID.`as`("bankId"),
                BANK.NAME.`as`("bankName"),
                BANK.SHORT_NAME.`as`("bankShortName"),
                BANK_CARD.NAME.`as`("name"),
                BANK_CARD.STATEMENT_DATE.`as`("statementDate"),
                BANK_CARD.PAYMENT_DUE_DATE.`as`("paymentDueDate"),
                BANK_CARD.CUSTOMER_SERVICE_NUMBER.`as`("customerServiceNumber"),
                BANK_CARD.IS_CREDIT_CARD.`as`("isCreditCard"),
                BANK_CARD.ENABLED.`as`("enabled"),
            )
            .from(BANK_CARD)
            .join(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
            .where(
                BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(BANK_CARD.ID.eq(id))
            )
            .fetchOneMap()

        if (bankInfo != null) {
            bankInfo["bankLogoUrl"] = bankService.getLogoUrl(bankInfo["bankShortName"] as String?)

            return bankInfo
        }

        return emptyMap()
    }

    /**
     * Adds new bank card for account group.
     *
     * @param accountGroupId: The string of user account group id.
     * @param data:           The map which contains keys with same name as bank card properties.
     * @param accountGroupId: The string of user account group id.
     */
    fun add(accountGroupId: String, @Valid data: CreateBankCardDto) {
        // Creates new BankCard with posted data.
        dslContext.newRecord(BANK_CARD).run {
            from(data)
            this.accountGroupId = accountGroupId
            bankId = data.bank.id.asString()
            insert()
        }
    }

    /**
     * Updates bank card info based on the provided map data and its id.
     * The map keys must match the bank card property names.
     * If the map item will be ignored if its value is null.
     *
     * @param accountGroupId The user account group ID.
     * @param data The `UpdateBankCardDto` which contains keys with the same name as bank card properties.
     */
    fun update(accountGroupId: String, @Valid data: UpdateBankCardDto) {
        if (dslContextWrapper.exists(
                BANK_CARD, BANK_CARD.ID.eq(data.id)
                    .and(BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId))
            )
        ) {
            dslContext.newRecord(BANK_CARD).run {
                from(data)
                bankId = data.bank.id.asString()

                // Retains the `id` for the WHERE clause but excludes it from the SET clause.
                changed(BANK_CARD.ID, false)

                update()
            }
        }
    }

    /**
     * Deletes the BankCard with the provided id.
     *
     * @param accountGroupId The user account group ID.
     * @param id The bank card id.
     * @return Boolean indicating if the deletion was successful.
     */
    fun delete(accountGroupId: String, id: String): Boolean {
        var isDeleted = false

        // Queries the input id and makes sure it belongs to the specific account group.
        // This is an example showing how to secure the data by taking extra action.
        // This can void other account group users from calling the API directly and providing IDs
        // which they don't actually own.
        // AKA parallel authentication.

        // Checks if this bank card is used by income/expense.
        if (!dslContextWrapper.exists(
                INCOME_EXPENSE_DETAIL, INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(id))
            )
        ) {
            // If this bank card is not used, then delete it.
            dslContext.deleteFrom(BANK_CARD)
                .where(
                    BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(BANK_CARD.ID.eq(id))
                )
                .execute()

            isDeleted = true
        }

        return isDeleted
    }

    /**
     * Deletes the BankCards with the provided list of IDs.
     *
     * @param accountGroupId The user account group ID.
     * @param ids The list of IDs to be deleted.
     */
    fun delete(accountGroupId: String, ids: List<String>) {
        // Queries the input id list and makes sure they belong to the specific account group.
        // This is an example shows how to secure the data by taking extra action.
        // This can void other account group users call api directly and provide these ids which they don't actually own them.
        // AKA parallel authentication.
        val validIds = queryAccountGroupBankCardIds(ids, accountGroupId).toMutableList()

        // Queries all bank card IDs that are used by income/expense.
        val usedIds = incomeExpenseDetailService.queryUsedBankCardIds(validIds, accountGroupId)

        // Excludes all used bank card IDs.
        validIds.removeAll(usedIds)

        if (validIds.isNotEmpty()) {
            // Executes batch deletion using DSL context for any unused bank card.
            dslContext.deleteFrom(BANK_CARD)
                .where(BANK_CARD.ID.`in`(validIds))
                .execute()
        }
    }

    /**
     * Queries the input ID list and ensures they belong to the specific account group.
     *
     * @param accountGroupId The user account group ID.
     * @param ids The list of IDs to be verified.
     * @return The list of matched IDs.
     */
    private fun queryAccountGroupBankCardIds(ids: List<String>, accountGroupId: String): List<String> {
        return if (ids.isNotEmpty()) {
            dslContext.selectFrom(BANK_CARD)
                .where(
                    BANK_CARD.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(BANK_CARD.ID.`in`(ids))
                )
                .fetch(BANK_CARD.ID)
        } else {
            emptyList()
        }
    }
}